package com.kmxd.ams.infra.tmpl.util;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import com.kmxd.ams.infra.tmpl.dto.JsonFieldDto;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.util.Assert;

/**
 * 生成表工具类
 *
 * @author ZuoLG
 */
@Component
@Slf4j
public class CreateTableUtils {

  /** 不需要添加数据长度的类型 */
  private static final String[] jdbcTime =
      new String[] {
        "data", "datetime", "time", "timestamp", "DATA", "DATETIME", "TIME", "TIMESTAMP"
      };

  private static final String[] jdbcText = new String[] {"longtext", "text", "TEXT", "LONGTEXT"};
  private static final String[] pgInt = new String[] {"int", "INT"};
  private static final String[] bigInt = new String[] {"bigint", "BIGINT"};
  private static final String[] tinyInt = new String[] {"tinyint", "TINYINT"};
  /** 主键字段 */
  private String primaryKey = "id";
  /** 数据库引擎 */
  private String dbEngine = "InnoDB";
  /** 字符编码集 */
  private String characterSet = "utf8mb4";

  /**
   * 获取创建表sql
   *
   * @param tableName 表英文名称
   * @param tableNameCn 表中文名称
   * @param fields 字段
   * @return
   */
  public String getTableSql(String tableName, String tableNameCn, List<JsonFieldDto> fields) {
    return getTableSql(tableName, tableNameCn, fields, false);
  }

  /**
   * 获取创建表sql
   *
   * @param tableName 表英文名称
   * @param tableNameCn 表中文名称
   * @param fieldDatas 字段
   * @param isDrop 是否删除已经存在的表
   * @return
   */
  public String getTableSql(
      String tableName, String tableNameCn, List<JsonFieldDto> fieldDatas, boolean isDrop) {
    Assert.hasText(tableName, "表名不能为空！");
    // 中文名称为空默认取表明设置
    tableNameCn = StringUtils.isNotBlank(tableNameCn) ? tableNameCn : tableName;
    List<JsonFieldDto> fields =
        fieldDatas.stream()
            .map(
                item -> {
                  item.setDbSort(ObjectUtil.isEmpty(item.getDbSort()) ? 200 : item.getDbSort());
                  item.setSort(ObjectUtil.isEmpty(item.getSort()) ? 200 : item.getSort());
                  return item;
                })
            .sorted(Comparator.comparing(JsonFieldDto::getDbSort))
            .collect(Collectors.toList());
    // 排序
    fields.sort(Comparator.comparing(JsonFieldDto::getDbSort));
    // 表sql对象
    StringBuffer tableSql = new StringBuffer();
    if (isDrop) {
      tableSql.append("DROP TABLE IF EXISTS `").append(tableName).append("`;  ");
    }
    tableSql.append("CREATE TABLE `").append(tableName).append("`  (");
    fields.forEach(
        field -> {
          if (primaryKey.equalsIgnoreCase(field.getCode())) {
            tableSql
                .append("`")
                .append(field.getCode())
                .append("` ")
                .append(getFieldLen(field.getType(), field.getLength()))
                .append(" NOT NULL COMMENT '")
                .append(field.getName())
                .append("', ");
          } else {
            String defValue =
                StringUtils.isNotBlank(field.getDefaultvalue())
                    ? "'" + field.getDefaultvalue() + "'"
                    : "";
            tableSql
                .append("`")
                .append(field.getCode())
                .append("` ")
                .append(getFieldLen(field.getType(), field.getLength()));
            if (StrUtil.isNotBlank(defValue)) {
              tableSql.append(" DEFAULT " + defValue);
            }
            tableSql.append(" COMMENT '").append(field.getName()).append("', ");
          }
        });
    // 添加索引
    String dbIndexKeys =
        fields.stream()
            .filter(
                x -> ObjectUtil.isNotEmpty(x.getDbIndex()) && ObjectUtil.equals(x.getDbIndex(), 1))
            .map(
                item -> {
                  return StrUtil.format("`{}`", item.getCode());
                })
            .collect(Collectors.joining(","));

    String dbIndexName = StrUtil.format("index_{}", RandomUtil.randomInt());

    tableSql
        .append("PRIMARY KEY (`")
        .append(primaryKey)
        .append("`) USING BTREE , ")
        .append(" KEY `" + dbIndexName + "` (" + dbIndexKeys + ") USING BTREE ")
        .append(") ENGINE = ")
        .append(dbEngine)
        .append(" CHARACTER SET = ")
        .append(characterSet)
        .append(" COLLATE = ")
        .append(characterSet)
        .append("_general_ci COMMENT = '")
        .append(tableNameCn)
        .append("' ROW_FORMAT = DYNAMIC;");
    return tableSql.toString();
  }

  /**
   * pg、人大金仓数据库
   *
   * @param tableName
   * @param tableNameCn
   * @param fieldDatas
   * @return
   */
  public String getTableSqlPg(String tableName, String tableNameCn, List<JsonFieldDto> fieldDatas) {
    Assert.hasText(tableName, "表名不能为空！");
    // 中文名称为空默认取表明设置
    tableNameCn = StringUtils.isNotBlank(tableNameCn) ? tableNameCn : tableName;
    // 排序
    fieldDatas.sort(Comparator.comparing(JsonFieldDto::getSort));
    // 表sql对象
    StringBuffer tableSql = new StringBuffer();
    tableSql.append(StrUtil.format("CREATE TABLE \"public\".\"{}\" ( ", tableName));
    String fieldStrSql =
        fieldDatas.stream()
            .map(
                field -> {
                  String sql =
                      StrUtil.format(
                          "  \"{}\" {} DEFAULT {}",
                          field.getCode(),
                          getFieldLenPG(field.getType(), field.getLength()),
                          StrUtil.isBlank(field.getDefaultvalue())
                              ? null
                              : StrUtil.format("'{}'", field.getDefaultvalue()));

                  return sql;
                })
            .collect(Collectors.joining(","));

    tableSql.append(fieldStrSql);
    tableSql.append(" );");
    return tableSql.toString();
  }



  /**
   * 达梦数据库
   *
   * @param tableName
   * @param tableNameCn
   * @param fieldDatas
   * @return
   */
  public String getTableSqlDm(String tableName, String tableNameCn, List<JsonFieldDto> fieldDatas) {
    Assert.hasText(tableName, "表名不能为空！");
    // 中文名称为空默认取表明设置
    tableNameCn = StringUtils.isNotBlank(tableNameCn) ? tableNameCn : tableName;
    // 排序
    fieldDatas.sort(Comparator.comparing(JsonFieldDto::getSort));
    // 表sql对象
    StringBuffer tableSql = new StringBuffer();
    tableSql.append(StrUtil.format("CREATE TABLE \"{}\" ( ", tableName));
    String fieldStrSql =
            fieldDatas.stream()
                    .map(
                            field -> {
                              String sql =
                                      StrUtil.format(
                                              "  \"{}\" {} DEFAULT {}",
                                              field.getCode(),
                                              getFieldLenDM(field.getType(), field.getLength()),
                                              StrUtil.isBlank(field.getDefaultvalue())
                                                      ? null
                                                      : StrUtil.format("'{}'", field.getDefaultvalue()));

                              return sql;
                            })
                    .collect(Collectors.joining(","));

    tableSql.append(fieldStrSql);
    tableSql.append(" );");
    return tableSql.toString();
  }


  /**
   * 获取字段及字段长度
   *
   * @param type 字段类型
   * @param length 字段长度
   * @return
   */
  private String getFieldLen(String type, Integer length) {
    if (ArrayUtil.contains(jdbcTime, type.toLowerCase())
        || ArrayUtil.contains(jdbcText, type.toLowerCase())) {
      return type;
    }
    return type + "(" + length + ")";
  }

  /**
   * 获取字段及字段长度
   *
   * @param type 字段类型
   * @param length 字段长度
   * @return
   */
  public String getFieldLenPG(String type, Integer length) {
    if (ArrayUtil.contains(jdbcTime, type.toLowerCase())) {
      return "timestamp(6)";
    }
    if (ArrayUtil.contains(pgInt, type.toLowerCase())) {
      return "int4";
    }
    if (ArrayUtil.contains(bigInt, type.toLowerCase())) {
      return "int8";
    }
    if (ArrayUtil.contains(tinyInt, type.toLowerCase())) {
      return "int2";
    }
    if (ArrayUtil.contains(jdbcText, type.toLowerCase())) {
      return "text";
    }
    return type + "(" + length + ")";
  }

  /**
   * 获取字段及字段长度
   *
   * @param type 字段类型
   * @param length 字段长度
   * @return
   */
  public String getFieldLenDM(String type, Integer length) {
    if (ArrayUtil.contains(jdbcTime, type.toLowerCase())) {
      return "TIMESTAMP(0)";
    }
    if (ArrayUtil.contains(jdbcText, type.toLowerCase())) {
      return "clob";
    }
    if (ArrayUtil.contains(pgInt, type.toLowerCase())) {
      return "INT";
    }
    if (ArrayUtil.contains(bigInt, type.toLowerCase())) {
      return "BIGINT";
    }
    if (ArrayUtil.contains(tinyInt, type.toLowerCase())) {
      return "TINYINT";
    }
    return type + "(" + length + ")";
  }

  public void setDbEngine(String dbEngine) {
    this.dbEngine = dbEngine;
  }

  public void setCharacterSet(String characterSet) {
    this.characterSet = characterSet;
  }

  public void setPrimaryKey(String primaryKey) {
    this.primaryKey = primaryKey;
  }
}
